CREATE
DATABASE if not exists `csc_member`;

use `csc_member`;

-- ----------------------------
-- Table structure for member_user
-- TODO 会员表反范式设计、冷热数据分离
-- ----------------------------
DROP TABLE IF EXISTS `member_user`;
CREATE TABLE `member_user`
(
    `id`            bigint(19) NOT NULL AUTO_INCREMENT COMMENT '编号',
    `nickname`      varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci           DEFAULT '' COMMENT '用户昵称',
    `id_card_type`  tinyint NULL COMMENT '证件类型：1 身份证，2 军官证，3 护照',
    `id_card_no`    varchar(30) NULL COMMENT '证件号',
    `name`          varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci           DEFAULT '' COMMENT '真实姓名',
    `status`        tinyint                                                       NOT NULL DEFAULT 0 COMMENT '状态',
    `gender`        tinyint                                                                DEFAULT NULL COMMENT '性别',
    `birthday`      datetime null comment '出生日期',
    `area_id`       int null comment '所在地',
    `mark`          varchar(255) null comment '用户备注',
    `score`         int                                                           NOT NULL DEFAULT 0 COMMENT '积分',
    `avatar`        varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '头像',
    `mobile`        varchar(15) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci  NOT NULL COMMENT '手机号',
    `email`         varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL COMMENT '邮箱',
    `password`      varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '密码',
    `register_type` char(2) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL COMMENT '注册类型, 10码云, 20钉钉, 30企业微信, 31微信公众号, 32微信开放平台, 34微信小程序',
    `open_id`       varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT '' COMMENT 'openid',
    `union_id`      varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT '' COMMENT 'unionid',
    `token`         varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT '' COMMENT 'token',
    `pay_password`  varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci          DEFAULT '' COMMENT '支付密码',
    `register_ip`   varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci           DEFAULT NULL COMMENT '注册 IP',
    `login_ip`      varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT '' COMMENT '最后登录IP',
    `login_date`    datetime NULL DEFAULT NULL COMMENT '最后登录时间',
    `creator`       varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT '' COMMENT '创建者',
    `create_time`   datetime                                                      NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    `updater`       varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT '' COMMENT '更新者',
    `update_time`   datetime                                                      NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    `deleted`       bit(1)                                                        NOT NULL DEFAULT b'0' COMMENT '是否删除',
    `tenant_id`     bigint(19) NOT NULL DEFAULT 1 COMMENT '租户编号',
    PRIMARY KEY (`id`) USING BTREE,
    UNIQUE INDEX `uk_mobile_tenant` (`mobile` ASC,`tenant_id`) USING BTREE COMMENT '手机号',
    UNIQUE INDEX `uk_email_tenant` (`email` ASC,`tenant_id`) USING BTREE COMMENT '邮箱'
) ENGINE = InnoDB
  CHARACTER SET = utf8mb4
  COLLATE = utf8mb4_general_ci COMMENT = '会员表';

-- ----------------------------
-- Records of member_user
-- ----------------------------
BEGIN;
COMMIT;


-- ----------------------------
-- Table structure for member_address
-- ----------------------------
DROP TABLE IF EXISTS `member_address`;
CREATE TABLE `member_address`
(
    `id`             bigint (19) NOT NULL AUTO_INCREMENT COMMENT '编号',
    `user_id`        bigint (19) NOT NULL COMMENT '会员编号',
    `name`           varchar(50)                                                   NOT NULL DEFAULT '' COMMENT '收件人名称',
    `mobile`         varchar(15) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci  NOT NULL COMMENT '手机号',
    `province_id`    bigint                                                                 DEFAULT NULL COMMENT '省ID',
    `province`       varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci          DEFAULT NULL COMMENT '省',
    `city_id`        bigint                                                                 DEFAULT NULL COMMENT '城市ID',
    `city`           varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci           DEFAULT NULL COMMENT '城市',
    `area_id`        bigint                                                                 DEFAULT NULL COMMENT '区县ID',
    `area`           varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci           DEFAULT NULL COMMENT '区县',
    `post_code`      varchar(16) COMMENT '邮编',
    `detail_address` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '收件详细地址',
    `defaulted`      bit(1)                                                        NOT NULL DEFAULT b'0' COMMENT '是否默认',
    `creator`        varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT '' COMMENT '创建者',
    `create_time`    datetime                                                      NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    `updater`        varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT '' COMMENT '更新者',
    `update_time`    datetime                                                      NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    `deleted`        bit(1)                                                        NOT NULL DEFAULT b'0' COMMENT '是否删除',
    `tenant_id`      bigint (19) NOT NULL DEFAULT 1 COMMENT '租户编号',
    PRIMARY KEY (`id`) USING BTREE,
    INDEX            `user_id` (`user_id` ASC) USING BTREE COMMENT '会员编号',
    INDEX            `mobile` (`mobile` ASC) USING BTREE COMMENT '手机号'
) ENGINE = InnoDB
    CHARACTER SET = utf8mb4
    COLLATE = utf8mb4_general_ci COMMENT '用户收件地址';

-- ----------------------------
-- Records of member_address
-- ----------------------------
BEGIN;
COMMIT;

-- ----------------------------
-- Table structure for area
-- ----------------------------
DROP TABLE IF EXISTS `area`;
CREATE TABLE `area`
(
    `id`          bigint                                                       NOT NULL AUTO_INCREMENT COMMENT '区域ID',
    `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    `update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    `area_name`   varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '地址',
    `parent_id`   bigint                                                       NOT NULL COMMENT '上级地址',
    `level`       int                                                          NOT NULL COMMENT '等级（从1开始）',
    PRIMARY KEY (`id`) USING BTREE,
    KEY           `parent_id` (`parent_id`) USING BTREE COMMENT '上级id'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='省市区地区信息';

/*Table structure for table `undo_log` */
DROP TABLE IF EXISTS `undo_log`;
CREATE TABLE `undo_log`
(
    `id`            bigint                                                        NOT NULL AUTO_INCREMENT,
    `branch_id`     bigint                                                        NOT NULL,
    `xid`           varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
    `context`       varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
    `rollback_info` longblob                                                      NOT NULL,
    `log_status`    int                                                           NOT NULL,
    `log_created`   datetime                                                      NOT NULL,
    `log_modified`  datetime                                                      NOT NULL,
    PRIMARY KEY (`id`) USING BTREE,
    UNIQUE KEY `ux_undo_log` (`xid`,`branch_id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=388 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- ----------------------------
-- Table structure for member_role
-- ----------------------------
DROP TABLE IF EXISTS member_role;
CREATE TABLE member_role
(
    `id`          BIGINT       NOT NULL AUTO_INCREMENT COMMENT '主键',
    `name`        VARCHAR(30)  NOT NULL COMMENT '角色名称',
    `code`        VARCHAR(100) NOT NULL COMMENT '角色权限字符串',
    `sort`        TINYINT      NOT NULL DEFAULT 0 COMMENT '显示顺序',
    `type`        TINYINT      NOT NULL COMMENT '角色类型;(10选品平台角色，20选品企业角色)',
    `status`      TINYINT      NOT NULL COMMENT '角色状态',
    `remark`      VARCHAR(300) COMMENT '备注',
    `creator`     VARCHAR(64) COMMENT '创建人',
    `create_time` DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    `updater`     VARCHAR(64) COMMENT '更新人',
    `update_time` DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '更新时间',
    `deleted`     BIT(1)       NOT NULL DEFAULT b'0' COMMENT '是否删除',
    `tenant_id`   BIGINT       NOT NULL DEFAULT 1 COMMENT '租户ID',
    PRIMARY KEY (id),
    UNIQUE KEY `code_tenant_id` (`code`,`tenant_id`) USING BTREE
) COMMENT = '会员角色';

-- ----------------------------
-- Records of member_role
-- ----------------------------
BEGIN;
insert into `member_role` (`id`, `name`, `code`, `sort`, `status`, `type`, `remark`, `creator`, `create_time`, `updater`, `update_time`, `deleted`, `tenant_id`)
values('1','采购商','purchaser','1','0','1','采购商','admin','2021-01-05 17:03:48','','2022-02-22 05:08:21','',130);

COMMIT;

-- ----------------------------
-- Table structure for member_user_role
-- ----------------------------
DROP TABLE IF EXISTS member_user_role;
CREATE TABLE member_user_role
(
    `id`          BIGINT NOT NULL AUTO_INCREMENT COMMENT '自增编号',
    `user_id`     BIGINT NOT NULL COMMENT '用户ID',
    `role_id`     BIGINT NOT NULL COMMENT '角色ID',
    `creator`     VARCHAR(64) COMMENT '创建者',
    `create_time` DATETIME        DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    `updater`     VARCHAR(64) COMMENT '更新者',
    `update_time` DATETIME        DEFAULT CURRENT_TIMESTAMP COMMENT '更新时间',
    `deleted`     BIT(1)          DEFAULT b'0' COMMENT '是否删除',
    `tenant_id`   BIGINT NOT NULL DEFAULT 0 COMMENT '租户编号',
    PRIMARY KEY (id)
) COMMENT = '会员和角色关联表';

-- ----------------------------
-- Table structure for product_browsing_history
-- ----------------------------
DROP TABLE IF EXISTS product_browsing_history;
CREATE TABLE product_browsing_history
(
    `id`          BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键ID',
    `user_id`     BIGINT NOT NULL COMMENT '用户Id',
    `spu_id`      BIGINT NOT NULL COMMENT '商品ID',
    `view_time`   DATETIME        DEFAULT CURRENT_TIMESTAMP COMMENT '最新浏览时间',
    `view_count`  INT    NOT NULL DEFAULT 0 COMMENT '浏览次数',
    `creator`     VARCHAR(64) COMMENT '创建人',
    `create_time` DATETIME        DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    `updater`     VARCHAR(64) COMMENT '更新人',
    `update_time` DATETIME        DEFAULT CURRENT_TIMESTAMP COMMENT '更新时间',
    `deleted`     BIT(1)          DEFAULT b'0' COMMENT '是否删除(0:否，1是)',
    `tenant_id`   BIGINT NOT NULL DEFAULT 1 COMMENT '租户ID',
    PRIMARY KEY (id),
    UNIQUE KEY `user_id_spu_id` (`user_id`,`spu_id`, `tenant_id`) USING BTREE
) COMMENT = '商品浏览记录';

-- ----------------------------
-- Table structure for member_menu
-- ----------------------------
DROP TABLE IF EXISTS member_menu;
CREATE TABLE member_menu
(
    `id`             BIGINT       NOT NULL AUTO_INCREMENT COMMENT '菜单ID',
    `name`           VARCHAR(50)  NOT NULL COMMENT '菜单名称',
    `permission`     VARCHAR(100) NOT NULL COMMENT '权限标识',
    `type`           TINYINT      NOT NULL COMMENT '菜单类型',
    `sort`           INT          NOT NULL DEFAULT 0 COMMENT '显示顺序',
    `parent_id`      BIGINT       NOT NULL DEFAULT 0 COMMENT '父菜单ID',
    `path`           VARCHAR(200) COMMENT '路由地址',
    `icon`           VARCHAR(100)          DEFAULT '#' COMMENT '菜单图标',
    `component`      VARCHAR(255) COMMENT '组件路径',
    `component_name` VARCHAR(255) COMMENT '组件名',
    `status`         TINYINT      NOT NULL DEFAULT 0 COMMENT '菜单状态',
    `visible`        BIT(1)       NOT NULL DEFAULT b'1' COMMENT '是否可见',
    `keep_alive`     BIT(1)       NOT NULL DEFAULT b'1' COMMENT '是否缓存',
    `always_show`    BIT(1)       NOT NULL DEFAULT b'1' COMMENT '是否总是显示',
    `creator`        VARCHAR(64) COMMENT '创建者',
    `create_time`    DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    `updater`        VARCHAR(64) COMMENT '更新者',
    `update_time`    DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '更新时间',
    `deleted`        BIT(1)       NOT NULL DEFAULT b'0' COMMENT '是否删除',
    `tenant_id`      BIGINT       NOT NULL DEFAULT 1 COMMENT '租户ID',
    PRIMARY KEY (id)
) COMMENT = '菜单权限表';

-- ----------------------------
-- Table structure for member_role_menu
-- ----------------------------
DROP TABLE IF EXISTS member_role_menu;
CREATE TABLE member_role_menu
(
    `id`          BIGINT   NOT NULL AUTO_INCREMENT COMMENT '自增编号',
    `role_id`     BIGINT   NOT NULL COMMENT '角色ID',
    `menu_id`     BIGINT   NOT NULL COMMENT '菜单ID',
    `creator`     VARCHAR(64) COMMENT '创建者',
    `create_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    `updater`     VARCHAR(64) COMMENT '更新者',
    `update_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '更新时间',
    `deleted`     BIT(1)   NOT NULL DEFAULT b'0' COMMENT '是否删除',
    `tenant_id`   BIGINT   NOT NULL DEFAULT 0 COMMENT '租户编号',
    PRIMARY KEY (id)
) COMMENT = '角色和菜单关联表';






